package com.excelcreatetable.demo.createtable;/**
 * @author wzj
 * @date 2020/3/17 14:11
 * @version 1.0
 */

import com.excelcreatetable.demo.dbconnect.DBConnect;
import com.excelcreatetable.demo.easyexcelreadhead.ReadHead;
import com.excelcreatetable.demo.saveData.SaveData;
import com.excelcreatetable.demo.threadLocalShare.ThreadLocalShare;
import com.opencsv.CSVReader;
import lombok.extern.slf4j.Slf4j;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.io.*;
import java.sql.*;
import java.util.*;




/**
 * auther wzj
 * date 2020/3/17 14:11
 */
@Slf4j
@Controller
@RequestMapping("/test")
public class CreateTable {




    @Autowired
    private ReadHead readHead;


    private CallableStatement call;


    @GetMapping("/createTable")
    @ResponseBody
    public void createTable(String filePath, String tableName) throws IOException, SQLException {

        //读取csv
        if (filePath.endsWith(".csv")){
            new Thread(new Runnable() {
                @Override
                public void run() {
                    try {
                        log.info("接受到的文件是一个csv文件接受到的文件地址为==:"+filePath+"=====创建的表为:"+tableName);
                        readCsv(filePath,tableName);
                    } catch (IOException e) {
                        e.printStackTrace();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }).start();
        }else {

                    log.info("接受到的文件是一个excel文件接受到的文件地址为==:"+filePath+"====创建的表为:"+tableName);
                    ThreadLocalShare.filePath.set(filePath);
                    readHead.readHead(filePath,tableName);
         }
    }

    //读取csv
    private void readCsv(String filePath, String tableName) throws IOException, SQLException {

        BufferedReader bufferedReader;
        //判断文件编码
        File file = new File(filePath);
        InputStream in= new java.io.FileInputStream(file);
        byte[] b = new byte[3];
        in.read(b);
        in.close();
        InputStreamReader isr;
        if (b[0] == -17 && b[1] == -69 && b[2] == -65){
            System.out.println("文件编码是utf-8");
            isr=new InputStreamReader(new FileInputStream(file),"utf-8");
        }else {
            System.out.println("文件编码是gbk");
            isr=new InputStreamReader(new FileInputStream(file),"GBK");
        }

        CSVReader reader = new CSVReader(isr);
        List<String[]> myEntries = reader.readAll();
        //创建表SQL
        StringBuilder sql =new StringBuilder("create table "+tableName+"(id int auto_increment primary key not null") ;
        //列
        StringBuffer createCloumns = new StringBuffer();
        StringBuffer headName = new StringBuffer();
        sql.append(",");
        if (myEntries!=null && myEntries.size()>0){
            //创建表头
            String[] cloumns = myEntries.get(0);
            for (int i = 0; i < cloumns.length; i++) {
                String column = cloumns[i];
                String[] split = column.split("/");
                if (i==cloumns.length-1){
                    createCloumns.append(split[0]);
                    headName.append(split[0]);
                }else {
                    createCloumns.append(split[0]).append(",");
                    headName.append(split[0]).append(",");
                }

                sql.append(split[0]+" varchar(255) DEFAULT NULL COMMENT "+"\""+split[1]+"\"");
                //sql+=hanziColum+" varchar(255) DEFAULT NULL COMMENT "+"\""+columName+"\"";
                //防止最后一个
                if (i<cloumns.length-1){
                    sql.append(",");
                    //sql+=",";
                }
            }
            //拼凑完 建表语句 设置默认字符集
            sql.append(")DEFAULT CHARSET=utf8;");
            //sql+=")DEFAULT CHARSET=utf8;";
            System.out.println("创建表的sql语句 = " + sql);
            log.info("csv创建"+tableName+"表的语句为:"+sql);

            try {
                //connection = dataSource.getConnection();
                Connection connection = DBConnect.getConnection();
                //查看表是否存在
                //Boolean table = isTable(connection, tableName);

                call = connection.prepareCall(sql.toString());
                boolean execute = call.execute(sql.toString());
                System.out.println("创建表成功");
                log.info("创建表成功");
                connection.close();
            } catch (SQLException e) {
                System.out.println("创建表失败");
                log.error("csv创建"+tableName+"表失败");
                e.printStackTrace();
            }

        }
        //获取连接
        Connection connection = DBConnect.getConnection();
        //批量插入
        connection.setAutoCommit(false);
        //预处理
        StringBuffer addSql = new StringBuffer("insert into "+tableName+"("+headName+")  values(");
        //表头长度
        String[] head = myEntries.get(0);
        for (int i = 0; i < head.length; i++) {
            if (i==head.length-1){
                addSql.append("?)");
            }else {
                addSql.append("?").append(",");
            }
        }
        log.info("添加的预处理SQL为:"+addSql);
        PreparedStatement ps = connection.prepareStatement(addSql.toString());
        //获取的数据
        //保存的数量
        int count = 0;
        for (int i = 1; i < myEntries.size(); i++) {
            String[] addCloumns = myEntries.get(i);
            StringBuffer buffer = new StringBuffer();
            String[] split1 = addCloumns.toString().split(",");
            for (int i1 = 0; i1 < addCloumns.length; i1++) {
                //拼接sql语句
                //没一列的数据
                String s=addCloumns[i1];
                if (i1==addCloumns.length-1){
                    if (StringUtils.isNotEmpty(s)){
                        //buffer.append("'").append(s).append("'");
                        ps.setString(i1+1,s);
                        //列的长度和数据长度对应不上
                        if (addCloumns.length<split1.length){
                            buffer.append(",");
                            for (int i2 = addCloumns.length; i2 < split1.length; i2++) {
                                //字段比数据多一列
                                if (addCloumns.length==split1.length-1){
                                    //buffer.append("null");
                                    ps.setString(i2+1,"null");
                                }else {
                                    if (i2==split1.length-1){
                                        //buffer.append("null");
                                        ps.setString(i2+1,"null");
                                    }else {
                                        buffer.append("null").append(",");
                                        ps.setString(i2+1,"null");
                                    }
                                }

                            }
                        }
                    }else {
                        ps.setString(i1+1,"null");
                       // buffer.append("null");
                        if (addCloumns.length<split1.length){
                            buffer.append(",");
                            for (int i2 = addCloumns.length; i2 < split1.length; i2++) {
                                if (addCloumns.length==split1.length-1){
                                    //buffer.append("null");
                                    ps.setString(i2+1,"null");
                                }else {
                                    if (i2==split1.length-1){
                                        //buffer.append("null");
                                        ps.setString(i2+1,"null");
                                    }else {
                                       //buffer.append("null").append(",");
                                        ps.setString(i2+1,"null");
                                    }
                                }

                            }
                        }
                    }
                } else {
                    if (StringUtils.isNotEmpty(s)){
                       // buffer.append("'").append(s).append("'").append(",");
                        ps.setString(i1+1,s);
                    }else {
                        //buffer.append("null").append(",");
                        ps.setString(i1+1,"null");
                    }
                }
            }
            ps.addBatch();
            if (i%5000==0){
                ps.executeBatch();
                connection.commit();
                ps.clearBatch();
                count+=5000;
                log.info("保存"+count+"条数据成功");
            }
        }
        ps.executeBatch();
        connection.commit();
        ps.clearBatch();
        log.info("解析文件==="+filePath+"===表为名:"+tableName+"==的csv数据解析完成");


        //保存数据的sql 一条一条保存
       /* for (int i = 1; i < myEntries.size(); i++) {
            String[] cloumns = myEntries.get(i);
            StringBuffer buffer = new StringBuffer();
            String[] split1 = cloumns.toString().split(",");
            for (int i1 = 0; i1 < cloumns.length; i1++) {
                //拼接sql语句
                //没一列的数据
                String s=cloumns[i1];
                if (i1==cloumns.length-1){
                    if (StringUtils.isNotEmpty(s)){
                        buffer.append("'").append(s).append("'");
                        //列的长度和数据长度对应不上
                        if (cloumns.length<split1.length){
                            buffer.append(",");
                            for (int i2 = cloumns.length; i2 < split1.length; i2++) {
                                //字段比数据多一列
                                if (cloumns.length==split1.length-1){
                                    buffer.append("null");
                                }else {
                                    if (i2==split1.length-1){
                                        buffer.append("null");
                                    }else {
                                        buffer.append("null").append(",");
                                    }
                                }

                            }
                        }
                    }else {
                        buffer.append("null");
                        if (cloumns.length<split1.length){
                            buffer.append(",");
                            for (int i2 = cloumns.length; i2 < split1.length; i2++) {
                                if (cloumns.length==split1.length-1){
                                    buffer.append("null");
                                }else {
                                    if (i2==split1.length-1){
                                        buffer.append("null");
                                    }else {
                                        buffer.append("null").append(",");
                                    }
                                }

                            }
                        }
                    }

                }else {
                    if (StringUtils.isNotEmpty(s)){
                        buffer.append("'").append(s).append("'").append(",");
                    }else {
                        buffer.append("null").append(",");
                    }
                }
            }
            String sql2 = "insert into "+ tableName+"("+cloumns.toString()+")"+" values("+buffer+")";
            System.out.println("添加的sql语句为 = " + sql2);
            log.info("csv创建"+tableName+"表的语句为:"+sql2);
            try {
                //TODO 一次性保存成功,不抛出异常,害怕丢失数据可以把异常抛出来
                PreparedStatement preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.executeUpdate();
                log.info("csv插入"+tableName+"表成功");
            } catch (Exception e) {
                //e.printStackTrace();
                //可以记录到日志里
                System.out.println("报错的sql为===" + sql2+"===行数为==="+i);
                log.error("csv插入"+tableName+"表失败"+"报错的sql为===" + sql2+"===行数为==="+i);
            }
        }*/
        connection.close();
    }

    /**
     *用excel建表
     * @param head 表的列名
     * @param tableName 表名
     */
    public void createTable(String[] head, String tableName){


        String[] clount = new String[head.length];
        if (head.length==0 || head==null){
            throw new RuntimeException("列为空");
        }

        StringBuilder sql =new StringBuilder("create table "+tableName+"(id int auto_increment primary key not null") ;
        //拼接语句
        if (head!=null && head.length>0){
            sql.append(",");
            //sql +=",";
            int length = head.length;
            for (int i = 0; i < length; i++) {
                //建表字段
                String columName = head[i].trim();
                String[] split = columName.split("/");
                //将汉字转化为拼音
                //String hanziColum = ToPinyin(columName);
                //将字段保存下来,方便保存数据
                clount[i]=split[0];
                sql.append(split[0]+" varchar(255) DEFAULT NULL COMMENT "+"\""+split[1]+"\"");
                //sql+=hanziColum+" varchar(255) DEFAULT NULL COMMENT "+"\""+columName+"\"";
                //防止最后一个
                if (i<length-1){
                    sql.append(",");
                    //sql+=",";
                }
            }
        }
        //拼凑完 建表语句 设置默认字符集
        sql.append(")DEFAULT CHARSET=utf8;");
        //sql+=")DEFAULT CHARSET=utf8;";
        System.out.println("创建表的sql语句 = " + sql);
        log.info("excel创建"+tableName+"表的sql为:"+sql);
        try {
            //connection = dataSource.getConnection();
            Connection connection = DBConnect.getConnection();
            //查看表是否存在
            //Boolean table = isTable(connection, tableName);

                call = connection.prepareCall(sql.toString());
                boolean execute = call.execute(sql.toString());
                System.out.println("创建表成功");
                log.info("excel创建"+tableName+"表成功");
                connection.close();
                //保存数据
                saveData2(tableName,clount);

        } catch (SQLException e) {
            System.out.println("创建表失败");
            log.error("excel创建"+tableName+"表失败");
            e.printStackTrace();
        }
    }

    /**
     * 将汉字转化为汉字
     * @param chinese
     * @return
     */
    public static String ToPinyin(String chinese){
        String pinyinStr = "";
        char[] newChar = chinese.toCharArray();
        HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
        defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        for (int i = 0; i < newChar.length; i++) {
            if (newChar[i] > 128) {
                try {
                    pinyinStr += PinyinHelper.toHanyuPinyinStringArray(newChar[i], defaultFormat)[0];
                } catch (BadHanyuPinyinOutputFormatCombination e) {
                    e.printStackTrace();
                }
            }else{
                pinyinStr += newChar[i];
            }
        }
        return pinyinStr;
    }

    /**
     * 保存数据
     * @param tableName 表名
     * @param column 列名
     */
    public void saveData2(String tableName,String[] column){
        String filePath = ThreadLocalShare.filePath.get();
        SaveData saveData = new SaveData();
        saveData.saveData(filePath,tableName,column);
    }

    public Boolean isTable(Connection connection,String tableName){
        Boolean isTable=true;
        try {
            ResultSet tables = connection.getMetaData().getTables(null, null, tableName, null);
            if (tables.next()){
                isTable=true;
            }else {
                isTable=false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return isTable;
    }


    public static void main(String[] args) throws IOException {

        String filePath="C:\\Users\\15934\\Desktop\\cardno_t_bank_card_info.csv";

        BufferedReader bufferedReader;
        //判断文件编码
        File file = new File(filePath);
        InputStream in= new java.io.FileInputStream(file);
        byte[] b = new byte[3];
        in.read(b);
        in.close();
        InputStreamReader isr;
        if (b[0] == -17 && b[1] == -69 && b[2] == -65){
            System.out.println("文件编码是utf-8");
             isr=new InputStreamReader(new FileInputStream(file),"utf-8");
        }else {
            System.out.println("文件编码是gbk");
             isr=new InputStreamReader(new FileInputStream(file),"GBK");
        }

        CSVReader reader = new CSVReader(isr);
        List<String[]> myEntries = reader.readAll();
        for (int i = 0; i < myEntries.size(); i++) {
            String[] cloumns = myEntries.get(i);
            for (int i1 = 0; i1 < cloumns.length; i1++) {
                System.out.println("i1 = " + cloumns[i1]);
            }
        }

    }


}

